using OrderSystem.SystemFramework;
using System;
using System.Data;
using System.Data.SqlClient;

namespace OrderSystem.DataAccess
{
	public class Operators : IDisposable
	{
		private SqlDataAdapter dataAdapter;

		private SqlCommand selectCommand;

		private SqlCommand insertCommand;

		private SqlCommand updateCommand;

		private SqlCommand deleteCommand;

		public DataSet Lonin(string sLoginName, string sPassword, string sIPAddress)
		{
			DataSet dataSet = new DataSet("Operator");
			try
			{
				this.selectCommand = new SqlCommand("Login", new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.CommandType = CommandType.StoredProcedure;
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@LoginName"].Value = sLoginName;
				this.selectCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@Password"].Value = sPassword;
				this.selectCommand.Parameters.Add(new SqlParameter("@IPAddress", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@IPAddress"].Value = sIPAddress;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataSet);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataSet;
		}

		public DataTable LoadUser(string loginName, string password)
		{
			string cmdText = "SELECT PKID,[Name],LoginName,[Password],RoleID FROM Operator WHERE LoginName=@LoginName AND [Password]=@Password";
			DataTable dataTable = new DataTable("Operator");
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@LoginName"].Value = loginName;
				this.selectCommand.Parameters["@Password"].Value = password;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool UpdatePassword(string loginName, string newPassword)
		{
			string cmdText = "UPDATE Operator SET Password=@Password WHERE LoginName=@LoginName";
			try
			{
				this.updateCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.updateCommand.Connection.Open();
				this.updateCommand.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@LoginName"].Value = loginName;
				this.updateCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar, 32));
				this.updateCommand.Parameters["@Password"].Value = newPassword;
				this.updateCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool InsertOperator(string operatorName, bool sex, string phone, string address, string email, string loginName, string password, int roleID, string remark)
		{
			string cmdText = "INSERT INTO Operator VALUES(@Name,@Sex,@Phone,@Address,@Email,@LoginName,@Password,@RoleID,@Remark)";
			try
			{
				this.insertCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@Name"].Value = operatorName;
				this.insertCommand.Parameters.Add(new SqlParameter("@Sex", SqlDbType.Bit));
				this.insertCommand.Parameters["@Sex"].Value = sex;
				this.insertCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@Phone"].Value = phone;
				this.insertCommand.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 128));
				this.insertCommand.Parameters["@Address"].Value = address;
				this.insertCommand.Parameters.Add(new SqlParameter("@Email", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@Email"].Value = email;
				this.insertCommand.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@LoginName"].Value = loginName;
				this.insertCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@Password"].Value = password;
				this.insertCommand.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int));
				this.insertCommand.Parameters["@RoleID"].Value = roleID;
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 128));
				this.insertCommand.Parameters["@Remark"].Value = remark;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool UpdateOperator(int operatorID, string operatorName, bool sex, string phone, string address, string email, string loginName, string password, int roleID, string remark)
		{
			string cmdText = "UPDATE Operator SET Name=@Name,Sex=@Sex,Phone=@Phone,Address=@Address,Email=@Email,LoginName=@LoginName,Password=@Password,RoleID=@RoleID,Remark=@Remark WHERE PKID=@PKID";
			try
			{
				this.insertCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@PKID", SqlDbType.Int));
				this.insertCommand.Parameters["@PKID"].Value = operatorID;
				this.insertCommand.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@Name"].Value = operatorName;
				this.insertCommand.Parameters.Add(new SqlParameter("@Sex", SqlDbType.Bit));
				this.insertCommand.Parameters["@Sex"].Value = sex;
				this.insertCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@Phone"].Value = phone;
				this.insertCommand.Parameters.Add(new SqlParameter("@Address", SqlDbType.NVarChar, 128));
				this.insertCommand.Parameters["@Address"].Value = address;
				this.insertCommand.Parameters.Add(new SqlParameter("@Email", SqlDbType.NVarChar, 64));
				this.insertCommand.Parameters["@Email"].Value = email;
				this.insertCommand.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@LoginName"].Value = loginName;
				this.insertCommand.Parameters.Add(new SqlParameter("@Password", SqlDbType.NVarChar, 32));
				this.insertCommand.Parameters["@Password"].Value = password;
				this.insertCommand.Parameters.Add(new SqlParameter("@RoleID", SqlDbType.Int));
				this.insertCommand.Parameters["@RoleID"].Value = roleID;
				this.insertCommand.Parameters.Add(new SqlParameter("@Remark", SqlDbType.NVarChar, 128));
				this.insertCommand.Parameters["@Remark"].Value = remark;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool OnLine(int operatorID, string ipAddress)
		{
			string cmdText = "INSERT LoginList VALUES(@OperatorID,@IPAddress,GETDATE())";
			try
			{
				this.insertCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@OperatorID"].Value = operatorID;
				this.insertCommand.Parameters.Add(new SqlParameter("@IPAddress", SqlDbType.VarChar, 32));
				this.insertCommand.Parameters["@IPAddress"].Value = ipAddress;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public bool DownLine(int operatorID)
		{
			string cmdText = "DELETE FROM LoginList WHERE OperatorID=@OperatorID";
			try
			{
				this.insertCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.insertCommand.Connection.Open();
				this.insertCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.insertCommand.Parameters["@OperatorID"].Value = operatorID;
				this.insertCommand.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return true;
		}

		public DataTable LoadOperator(int nOperatorID)
		{
			DataTable dataTable = new DataTable("Operator");
			string cmdText = "SELECT op.PKID AS 用户ID,op.[Name] AS 用户姓名,op.Sex AS 性别,op.Phone AS 电话,op.Address AS 地址,op.Email AS Email, op.LoginName AS 登陆名,op.Password as 密码,ro.PKID 角色ID,ro.[Name] as 角色名称, ro.[Description] as 描述, op.Remark AS 用户备注 from Operator as op inner join Role as ro on op.RoleID=ro.PKID WHERE op.PKID=@OperatorID";
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@OperatorID", SqlDbType.Int));
				this.selectCommand.Parameters["@OperatorID"].Value = nOperatorID;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public DataTable GetOperators(string strQuery)
		{
			DataTable dataTable = new DataTable("Operator");
			try
			{
				this.selectCommand = new SqlCommand(strQuery, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable;
		}

		public bool GetLoginState(string loginName)
		{
			string cmdText = "SELECT * FROM LoginList WHERE LoginName=@LoginName";
			DataTable dataTable = new DataTable("OperatorOnLine");
			try
			{
				this.selectCommand = new SqlCommand(cmdText, new SqlConnection(AppSettings.ConnectionString));
				this.selectCommand.Connection.Open();
				this.selectCommand.Parameters.Add(new SqlParameter("@LoginName", SqlDbType.VarChar, 32));
				this.selectCommand.Parameters["@LoginName"].Value = loginName;
				this.dataAdapter = new SqlDataAdapter(this.selectCommand);
				this.dataAdapter.Fill(dataTable);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			return dataTable.Rows.Count > 0;
		}

		public void Dispose()
		{
			this.Dispose(true);
			GC.SuppressFinalize(true);
		}

		protected virtual void Dispose(bool disposing)
		{
			if (disposing)
			{
				if (this.dataAdapter != null)
				{
					if (this.dataAdapter.SelectCommand != null)
					{
						if (this.dataAdapter.SelectCommand.Connection != null)
						{
							this.dataAdapter.SelectCommand.Connection.Dispose();
							this.dataAdapter.SelectCommand.Connection = null;
						}
						this.dataAdapter.SelectCommand.Dispose();
						this.dataAdapter.SelectCommand = null;
					}
					this.dataAdapter.Dispose();
					this.dataAdapter = null;
				}
				if (this.insertCommand != null)
				{
					if (this.insertCommand.Connection != null)
					{
						this.insertCommand.Connection.Dispose();
						this.insertCommand.Connection = null;
					}
					this.insertCommand.Dispose();
					this.insertCommand = null;
				}
				if (this.updateCommand != null)
				{
					if (this.updateCommand.Connection != null)
					{
						this.updateCommand.Connection.Dispose();
						this.updateCommand.Connection = null;
					}
					this.updateCommand.Dispose();
					this.updateCommand = null;
				}
				if (this.deleteCommand != null)
				{
					if (this.deleteCommand.Connection != null)
					{
						this.deleteCommand.Connection.Dispose();
						this.deleteCommand.Connection = null;
					}
					this.deleteCommand.Dispose();
					this.deleteCommand = null;
				}
			}
		}
	}
}
